import requests
import pymysql
class DingDwbApi:
    def __init__(self, app_key, app_secret, base_Id=None):
        # 如果没有提供base_Id，则使用全局变量baseId
        self.base_Id = base_Id if base_Id is not None else base_Id
        self.obase_url = "https://oapi.dingtalk.com"
        self.base_url = "https://api.dingtalk.com/v1.0/notable/bases"
        self.expires_at = 0
        self.app_key = app_key
        self.app_secret = app_secret
        self.access_token = self.get_access_token()

    def get_access_token(self) -> str:
        """
        获取access_token
        :return: access_token
        """
        url = f"{self.obase_url}/gettoken"
        params = {
            "appkey": self.app_key,
            "appsecret": self.app_secret,
        }
        response = requests.get(url,params)
        result = response.json()
        return result["access_token"]

    def get_operator_id(self,userid) -> str:
        """
        获取operatorId
        :return: operatorId
        """
        url = f"{self.obase_url}/topapi/v2/user/get"
        params = {
            "access_token": self.access_token,
        }
        data = {
            "userid": userid,  
            "language": "zh_CN",
        }
        response = requests.post(url, json=data, params=params)
        result = response.json()
        return result['result']['unionid']

    def get_user_id(self,unionid) -> str:
        """
        获取userid
        :return: userid
        """
        url = "https://oapi.dingtalk.com/topapi/user/getbyunionid"
        params = {
            "access_token": self.access_token,
        }
        data = {
            "unionid": unionid,  
        }
        response = requests.post(url, json=data, params=params)
        result = response.json()
        return result['result']['userid']

    def get_union_id(self,nick_name) -> str:
        """
        获取unionid
        :return: unionid
        """
        connection = None
        try:
            # 使用pymysql连接数据库
            connection = pymysql.connect(
                host='192.168.11.66',  # 数据库主机地址
                port=3306,
                user='ryms',  # 数据库用户名
                password='ryms6666',  # 数据库密码
                database='rymanagementsystem',  # 数据库名称
                charset='utf8mb4',  # 字符编码
                cursorclass=pymysql.cursors.DictCursor  # 使用字典游标
            )
            
            with connection.cursor() as cursor:
                # 查询unionid的SQL语句
                sql = "SELECT unionid FROM sys_user WHERE nick_name = %s"
                cursor.execute(sql, (nick_name))
                
                result = cursor.fetchone()
                
                if result:
                    return result['unionid']  # 返回查询到的unionid
                    
        except pymysql.Error as e:
            print(f"数据库连接错误: {e}")
            
        finally:
            # 关闭数据库连接
            if connection:
                connection.close()
        
    # 数据表
    def CreateSheet(self):
        """
        创建数据表
        :return:
        """
        pass

    def GetAllSheets(self,operatorId:str) -> dict:
        """
        获取所有数据表
        :return: 数据表列表
        """
        url = f"{self.base_url}/{self.base_Id}/sheets?operatorId={operatorId}"
        headers = {
            "Content-Type": "application/json",
            "x-acs-dingtalk-access-token": self.access_token,
        }
        response = requests.get(url,headers=headers)
        result = response.json()
        return result

    #记录
    def InsertRecords(self,sheetIdOrName:str,data:dict,operatorId:str) -> dict:
        """
        新增多行记录
        sheetIdOrName:数据表ID或者名称
        data:数据
        {
        "records": [{
            "fields": {
            "多行文本": "多行文本内容",
            "数字": 100,
            "单选": "选项1",
            "多选": ["选项1", "选项2"]
            }
        }, {
            "fields": {
                "多行文本": "多行文本内容_",
                "数字": 101,
                "单选": "选项2",
                "多选": ["选项1"]
            }
        }]
        }
        :return: 新增记录的结果
        """
        url = f"{self.base_url}/{self.base_Id}/sheets/{sheetIdOrName}/records?operatorId={operatorId}"
        headers = {
            "Content-Type": "application/json",
            "x-acs-dingtalk-access-token": self.access_token,
        }
        response = requests.post(url,headers=headers,json=data)
        result = response.json()
        return result

    #字段
    def GetALLFields(self,sheetIdOrName:str,operatorId:str) -> dict:

        """
        获取所有字段
        sheetIdOrName:数据表ID或者名称
        :return:字段列表
        """
        url = f"{self.base_url}/{self.base_Id}/sheets/{sheetIdOrName}/fields?operatorId={operatorId}"
        headers = {
            "Content-Type": "application/json",
            "x-acs-dingtalk-access-token": self.access_token,
        }
        response = requests.get(url,headers=headers)
        result = response.json()
        return result



if __name__ == "__main__":
    """
    应用:仁阳管理系统
    权限：多维表的读写权限
    """
    #应用凭证
    app_key = "dingdneaceu8xm5bjnup" 
    app_secret = "POY2oTaJgT4HwYdn65cu5bokiCmydZ0pehnkuUW_RP2LtxjhY9qxtcta_FLbFkD8"
    # 多维表ID
    base_Id = "gwva2dxOW446kNmgUdpq1KL0Wbkz3BRL"
    #权限名字 用来获取userid
    nick_name = "袁梦珂" 
    api = DingDwbApi(app_key=app_key, app_secret=app_secret, base_Id=base_Id)
    unionid = api.get_union_id(nick_name)
    userid = api.get_user_id(unionid)
    operator_id = api.get_operator_id(userid)
#获取所有字段
    all_sheets = api.GetAllSheets(operator_id)
    for item in all_sheets["value"]:
        print(item["name"])
        sheetIdOrName = item["name"]
        all_fields = api.GetALLFields(sheetIdOrName,operator_id)
        print(all_fields)
        break




    
#获取所有数据表
    # res = api.GetAllSheets()
    # print(res)
#获取所有字段
    # sheetIdOrName = "5oYGQXe"
    # res = api.GetALLFields(sheetIdOrName)
    # print(res)
#插入数据
    # sheetIdOrName = "5oYGQXe"
    # data = {
    #     "records": [{
    #         "fields": {
    #             "订单号": "10086",
    #             "产品": "产品A",
    #             "定购量": 123,
    #             "订单金额": "",
    #             "下单日期":datetime.now().replace(hour=0, minute=0, second=0, microsecond=0).timestamp(),
    #             "负责人":[{"unionId":api.get_operator_id()}]
    #         }
    #     }]
    # }
    # res = api.InsertRecords(sheetIdOrName = sheetIdOrName,data=data)
    # print(res)
